package com.fenrirtec.aepp.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fenrirtec.aepp.common.dao.TenderInformationDao;
import com.fenrirtec.aepp.common.model.TenderInformation;
import com.fenrirtec.core.database.DatabaseSessionManager;
import com.fenrirtec.core.database.ResultSetMapper;
import com.fenrirtec.core.exception.DatabaseException;
import com.fenrirtec.core.utils.DatabaseUtils;

public class TenderInformationDao_JDBCImpl implements TenderInformationDao {
	private static final Logger logger = LoggerFactory
			.getLogger(TenderInformationDao_JDBCImpl.class);

	@Override
	public int insertTenderInformation(
			TenderInformation tenderInformation) {
		if (logger.isInfoEnabled()) {
			logger.info("[TenderProductDao_JDBCImpl#insertTenderInformation] start.");
		}

		PreparedStatement statement = null;
		try {
			StringBuilder sql = new StringBuilder()
					.append("insert into t_tender_information ( \n")
					.append("  tender_no, \n")
					.append("  tender_title, \n")
					.append("  enroll_end_date, \n")
					.append("  tender_end_date, \n")
					.append("  publish_result_date, \n")
					.append("  content_description, \n")
					.append("  vendor_requirement, \n")
					.append("  tender_template, \n")
					.append("  contact_name, \n")
					.append("  contact_tel, \n")
					.append("  create_date, \n")
					.append("  tender_pattern) \n")
					.append("values ( \n")
					.append("  concat(?,get_random(4)) ,\n")
					.append("  ? ,\n")
					.append("  DATE_FORMAT(?,'%Y-%m-%d %H:%i:%s') ,\n")
					.append("  DATE_FORMAT(?,'%Y-%m-%d %H:%i:%s') ,\n")
					.append("  DATE_FORMAT(?,'%Y-%m-%d %H:%i:%s') ,\n")
					.append("  ? ,\n")
					.append("  ? ,\n")
					.append("  1 ,\n")
					.append("  ? ,\n")
					.append("  ? ,\n")
					.append("  NOW() ,\n")
					.append("  ? )\n")
					;
			if (logger.isTraceEnabled()) {
				logger.trace(
						"[TenderProductDao_JDBCImpl#insertTenderInformation] sql={}",
						sql);
			}
			Connection connection = DatabaseSessionManager.getInstance()
					.getSession();
			statement = connection.prepareStatement(sql.toString());

			Date nowTime = new Date(System.currentTimeMillis());
			SimpleDateFormat sdFormatter = new SimpleDateFormat("yyyyMMdd");
			String retStrFormatNowDate = sdFormatter.format(nowTime);

			statement.setString(1, retStrFormatNowDate);
			statement.setString(2, tenderInformation.getTenderTitle()
					.toString());
			statement.setString(3,
					tenderInformation.getEnrollEndDate());
			statement.setString(4,
					tenderInformation.getTenderEndDate());
			statement.setString(5,
					tenderInformation.getPublishResultDate());
			statement.setString(6,
					tenderInformation.getContentDescription());
			statement
					.setString(7, tenderInformation.getVendorRequirement());
			statement.setString(8, tenderInformation.getContactName());
			statement.setString(9, tenderInformation.getContactTel());
			statement.setInt(10, tenderInformation.getTenderPattern());
			statement.execute();
			int maxId = DatabaseUtils.getLastInsertId();
			return maxId;
		} catch (SQLException e) {
			if (logger.isTraceEnabled()) {
				logger.trace(
						"[TenderProductDao_JDBCImpl#insertTenderInformation] sql error occurred.",
						e);
			}
			throw new DatabaseException("database error occurred.", e);
		} finally {
			if (logger.isInfoEnabled()) {
				logger.info("[TenderProductDao_JDBCImpl#insertTenderInformation] finish.");
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	@Override
	public List<TenderInformation> findTenderInformation(Integer tenderid) {
		if(logger.isInfoEnabled()){
			logger.info("[TenderInformationDao_JDBCImpl#findTenderInformation] start.");
		}
		PreparedStatement statement=null;
		try{
	
			StringBuffer sql=new StringBuffer()
			.append("select \n")
			.append("tender_title, \n")
			.append("tender_pattern_name, \n")			
			.append("enroll_start_date, \n")
			.append("enroll_end_date, \n")			
			.append("tender_end_date, \n")
			.append("publish_result_date, \n")			
			.append("content_description, \n")		
			.append("vendor_requirement, \n")
			.append("contact_name, \n")	
			.append("contact_tel \n")
			.append("from \n")
			.append(" v_tender_information \n")					
			.append(" where \n")			
			.append(" tender_id= ? \n");
			
			if (logger.isTraceEnabled()) {
				logger.trace("[TenderInformationDao_JDBCImpl#findTenderInformation] sql={}", sql);
			}
			
			Connection connection = DatabaseSessionManager.getInstance().getSession();
			statement = connection.prepareStatement(sql.toString());
			statement.setInt(1, tenderid);
			ResultSet resultSet = statement.executeQuery();			
			ResultSetMapper<TenderInformation> resultSetMapper=new ResultSetMapper<TenderInformation>();
			List<TenderInformation> tenderInformation=resultSetMapper.mapResultSetToList(resultSet, TenderInformation.class);
			return tenderInformation;
		}catch(SQLException e){
			if (logger.isTraceEnabled()) {
				logger.trace("[TenderInformationDao_JDBCImpl#findTenderInformation] sql error occurred.", e);
			}
			throw new DatabaseException("database error occurred.", e);
		}finally{
			if (logger.isInfoEnabled()) {
				logger.info("[TenderInformationDao_JDBCImpl#findTenderInformation] finish.");
			}
			if (statement != null) { 
				try { 
					statement.close(); 
					} catch (SQLException e) {} 
				}
		}
	
	}
	
    @Override
    public Boolean update(TenderInformation tenderInformation) {
        if (logger.isInfoEnabled()) {
            logger.info("[TenderInformationDao_JDBCImpl#update] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            StringBuilder sql = new StringBuilder()
                .append("update \n")
                .append("  t_tender_information \n")
                .append("set \n");

            
            sql.append("  tender_title = ?, \n");
            sql.append("  content_description = ?, \n");
            sql.append("  vendor_requirement = ? ,\n");
            sql.append("  enroll_start_date = DATE_FORMAT(?,'%Y-%m-%d %H:%i:%s') ,\n");
            sql.append("  enroll_end_date = DATE_FORMAT(?,'%Y-%m-%d %H:%i:%s') ,\n");
            sql.append("  publish_result_date = DATE_FORMAT(?,'%Y-%m-%d %H:%i:%s') ,\n");
            sql.append("  contact_name = ? ,\n");
            sql.append("  contact_tel = ? ,\n");
            sql.append("  tender_pattern = ? \n");
            sql.append("where \n");
            sql.append("  tender_id = ? \n");
            sql.append("and \n");
            sql.append("  delete_flag = false \n");

            if (logger.isTraceEnabled()) {
                logger.trace("[TenderInformationDao_JDBCImpl#update] sql={}", sql);
            }
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            
            int index = 0;

            statement.setString(++index, tenderInformation.getTenderTitle());
            statement.setString(++index, tenderInformation.getContentDescription());
            statement.setString(++index, tenderInformation.getVendorRequirement());
            statement.setString(++index, tenderInformation.getEnrollStartDate());
            statement.setString(++index, tenderInformation.getEnrollEndDate());
            statement.setString(++index, tenderInformation.getPublishResultDate());
            
            statement.setString(++index, tenderInformation.getContactName());
            statement.setString(++index, tenderInformation.getContactTel());
            statement.setInt(++index, tenderInformation.getTenderPattern());
            statement.setInt(++index, tenderInformation.getTenderId());
            
            
            int count = statement.executeUpdate();
            result = (count == 1);
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[TenderInformationDao_JDBCImpl#update] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[TenderInformationDao_JDBCImpl#update] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
        return result;
    }
    
    @Override
    public Boolean delete(Integer tenderId) {
        if (logger.isInfoEnabled()) {
            logger.info("[TenderInformationDao_JDBCImpl#delete] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            StringBuilder sql = new StringBuilder()
                .append("update \n")
                .append("  t_tender_information \n")
                .append("set \n");

            
            sql.append("  delete_flag = TRUE \n");
 
            sql.append("where \n");
            sql.append("  tender_id = ? \n");
            if (logger.isTraceEnabled()) {
                logger.trace("[TenderInformationDao_JDBCImpl#delete] sql={}", sql);
            }
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            
            

            statement.setInt(1, tenderId);

            
            
            int count = statement.executeUpdate();
            result = (count == 1);
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[TenderInformationDao_JDBCImpl#delete] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[TenderInformationDao_JDBCImpl#delete] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
        return result;
    }

    @Override
    public Boolean updateStatus(TenderInformation tenderInformation) {
        if (logger.isInfoEnabled()) {
            logger.info("[TenderInformationDao_JDBCImpl#update] start.");
        }

        PreparedStatement statement = null;
        Boolean result = false;
        
        try {
            StringBuilder sql = new StringBuilder()
                .append("update \n")
                .append("  t_tender_information \n")
                .append("set \n");
            sql.append("  status = ? \n");
            sql.append("where \n");
            sql.append("  tender_id = ? \n");
            if (logger.isTraceEnabled()) {
                logger.trace("[TenderInformationDao_JDBCImpl#update] sql={}", sql);
            }
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            int index = 0;
            statement.setInt(++index, tenderInformation.getStatus());
            statement.setInt(++index, tenderInformation.getTenderId());
            int count = statement.executeUpdate();
            result = (count == 1);
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[TenderInformationDao_JDBCImpl#update] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[TenderInformationDao_JDBCImpl#update] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
        return result;
    }
}
